package yitian.study

import groovy.sql.GroovyRowResult
import groovy.sql.Sql

class SqlDatabase {
    static void main(String[] args) {
        def sql = setUpDatabase()
        setUpTables(sql)
        insertRow(sql)
        queryTable(sql)
        queryTableAsRows(sql)
        queryOneAuthor(sql)
        updateAuthor(sql)
        deleteRow(sql)
        sqlTransaction(sql)
        sqlBatch(sql)
        sqlPagination(sql)
        sqlMetaData(sql)
    }

    static Sql setUpDatabase() {
        println('准备数据库')
        def url = 'jdbc:hsqldb:mem:test'
        def user = 'sa'
        def password = ''
        def driver = 'org.hsqldb.jdbcDriver'
        def sql = Sql.newInstance(url, user, password, driver)
        println('准备数据库完成')
        return sql
    }

    static void setUpTables(Sql sql) {
        println('准备表')
        sql.execute('''
  CREATE TABLE author (
    id          INTEGER GENERATED BY DEFAULT AS IDENTITY,
    firstname   VARCHAR(64),
    lastname    VARCHAR(64)
  );
''')
        println('准备表完成')
    }

    static void insertRow(Sql sql) {
        println('插入数据')
        String stmt = 'insert into author(firstname,lastname) values(?,?)'
        def params = ['Yi', 'Tian']
        def id = sql.executeInsert(stmt, params)
        assert id[0] == [0]
        params = ['Zhang', 'San']
        id = sql.executeInsert(stmt, params)
        assert id[0] == [1]
    }

    static void queryTable(Sql sql) {
        println('查询数据')

        sql.eachRow('SELECT id,firstname,lastname FROM author') { row ->
            println "[id=${row[0]},first=${row[1]},last=${row[2]}"
        }
    }

    static void queryTableAsRows(Sql sql) {
        List authors = sql.rows('SELECT id,firstname,lastname FROM author')
        println("总共${authors.size()}条数据")
        authors.each { printRow(it) }
    }

    static void queryOneAuthor(Sql sql) {
        println('查询单个用户')
        def rows = sql.firstRow('SELECT id,firstname,lastname FROM author WHERE id=1')
        def name = rows.values().join(',')
        println(name)
    }

    static void updateAuthor(Sql sql) {
        def stmt = 'update author set firstname=?,lastname=? where id=?'
        def row = sql.executeUpdate(stmt, ['li', '4', 1])
        assert row == 1
    }

    static void deleteRow(Sql sql) {
        assert sql.firstRow('SELECT COUNT(*) AS num FROM author').num == 2
        def stmt = 'delete from author where id=1'
        sql.executeUpdate(stmt)
        assert sql.firstRow('SELECT COUNT(*) AS num FROM Author').num == 1
    }

    static void sqlTransaction(Sql sql) {
        println '事务管理'
        def rowsBefore = sql.firstRow('SELECT count(*) AS num FROM author').num
        try {
            sql.withTransaction {
                //正确语句
                sql.executeInsert("INSERT INTO author(firstname,lastname) VALUES('wang','5')")
                sql.executeInsert("INSERT INTO author() VALUES(4324,3423)")
            }
        } catch (ignore) {
            println(ignore.message)
        }
        def rowsAfter = sql.firstRow('SELECT count(*) AS num FROM author').num
        assert rowsBefore == rowsAfter
    }

    static void sqlBatch(Sql sql) {
        println("批处理")
        def preparedStatement = 'insert into author(firstname,lastname) values(?,?)'
        sql.withBatch(3, preparedStatement) {
            it.addBatch('nana', 'li')
            it.addBatch('lily', 'Smith')
            it.addBatch('王', '五')
            it.addBatch('赵', '六')
            it.addBatch('孙', '悟空')
            it.addBatch('猪', '八戒')
            it.addBatch('玄', '奘')
            it.addBatch('沙', '僧')
        }
        queryTable(sql)
    }

    static void sqlPagination(Sql sql) {
        println('分页')
        def stmt = 'select id,firstname,lastname from author'
        sql.rows(stmt, 1, 3).each { printRow(it) }
        println('----------------')
        sql.rows(stmt, 4, 3).each { printRow(it) }
        println('----------------')
        sql.rows(stmt, 7, 3).each { printRow(it) }
        println('----------------')
    }

    static void sqlMetaData(Sql sql) {
        println '元数据'
        def connection = sql.connection.metaData
        println("数据库驱动名称:${connection.driverName}")
        println("数据库版本号:${connection.databaseMajorVersion}.${connection.databaseMinorVersion}")
        println("数据库产品名:${connection.databaseProductName}")

        println('结果元数据')
        def metaClosure = { meta ->
            println("${meta.getColumnName(1)}\t${meta.getColumnName(2)}\t${meta.getColumnName(3)}")
        }
        def rowClosure = { row ->
            println "[id=${row[0]},first=${row[1]},last=${row[2]}"
        }
        sql.eachRow('SELECT id,firstname,lastname FROM author', metaClosure, rowClosure)
    }

    static void printRow(GroovyRowResult row) {
        println "[id=${row[0]},first=${row[1]},last=${row[2]}"
    }
}

